from django.db import models
from django.db import connection

class UserManager(models.Manager):
	def grab_all(self):
		cursor = connection.cursor()
		cursor.execute("""
			SELECT email, password, certifications, first_name, last_name, type
			FROM User""")
		result_list = []
		for row in cursor.fetchall():
			p = self.model(
				first_name=row[3],
				last_name=row[4],
				email=row[0],
				password=row[1],
				certifications=row[2],
				type=row[5])
			result_list.append(p)
		return result_list
		
	def getUserInfo(self,email):
		# returns a user's details given his/her email
		cursor = connection.cursor()
		cursor.execute("""
			SELECT email, password, certifications, first_name, last_name, type
			FROM User WHERE email=%s;"""%(email))
		result_list = []
		for row in cursor.fetchall():
			p = self.model(
				first_name=row[3],
				last_name=row[4],
				email=row[0],
				password=row[1],
				certifications=row[2],
				type=row[5])
			result_list.append(p)
		return result_list
			
	def getManagedProjects(self,email):
		#returns all projects a user manages given his/her email
		cursor = connection.cursor()
		cursor.execute("""
			SELECT Project.p_id , Project.title
			FROM Project 
			WHERE Project.manager = %s
			ORDER BY Project.p_id ASC;"""%(email))
		result_list = []
		for row in cursor.fetchall():
			p = Project(
				p_id=row[0],
				title=row[1])
			result_list.append(p)
		return result_list
		
		
class ProjectManager(models.Manager):

	#returns the title and description of of a project
	#given its id
	def getProjectInfo(self, projectID):
		cursor = connection.cursor()
		cursor.execute("""
			SELECT title, description 
			FROM Project
			WHERE p_id=%s;"""%(projectID))
		result_list = []
		for row in cursor.fetchall():
			p = self.model(
				title=row[0],
				description=row[1])
			result_list.append(p)
		return result_list
	
	#returns a project's name	
	def getProjectTitle(self, projectID):
		cursor = connection.cursor()
		cursor.execute("""
			SELECT name
			FROM Project
			WHERE p_id=%s;"""%(projectID))
		result_list = []
		for row in cursor.fetchall():
			p = self.model(
				name=row[0])
			result_list.append(p)
		return result_list
	
	#returns a project's title	
	def getProjectTitle(self, projectID):
		cursor = connection.cursor()
		cursor.execute("""
			SELECT title
			FROM Project
			WHERE p_id=%s;"""%(projectID))
		result_list = []
		for row in cursor.fetchall():
			p = self.model(
				title=row[0])
			result_list.append(p)
		return result_list
	
	#returns the first and last name of the manager of a project
	#given the projectID
	def getManagerName(self, projectID):	
		cursor = connection.cursor()
		cursor.execute("""
			SELECT first_name, last_name 
			FROM User RIGHT JOIN (
				SELECT manager
				FROM Project
				WHERE p_id=%s
			) as P ON User.email=P.manager;"""%(projectID))
		result_list = []
		for row in cursor.fetchall():
			p = User(
				first_name=row[0],
				last_name=row[1])
			result_list.append(p);
		return result_list
		
	#returns a list of workers on a project given a project ID
	def getProjectWorkers(self, projectID):
		cursor = connection.cursor()
		cursor.execute("""
			SELECT DISTINCT first_name, last_name, email 
			FROM (
				User INNER JOIN Project_Workers
				ON User.email=Project_Workers.employee
			)	WHERE project=%s;"""%(projectID))
		result_list = []
		for row in cursor.fetchall():
			p = User(
				first_name=row[0],
				last_name=row[1],
				email=row[2])
			result_list.append(p);
		return result_list
		
	#creates a new project with the given title, description
	#and manager 
	def newProject(self,title,description,manager):
		cursor = connection.cursor()
		cursor.execute("""
			INSERT INTO Project (title, description, manager)
			VALUES (%s,%s,%s);"""%(title,description, manager))
			#unfinished or not
	
	#returns a list of all employees		
	def viewAllEmployees(self):
		cursor = connection.cursor()
		cursor.execute("""
			SELECT first_name, last_name, email FROM User;""")
		result_list = []
		for row in cursor.fetchall():
			p = User(
				first_name=row[0],
				last_name=row[1],
				email=row[2])
			result_list.append(p);
		return result_list	
	
	#TODO write this method. Not sure what i should be doing
	#adds workers to the project workers
	def addEmployees(self, empList,projectID):
		cursor = connection.cursor()
		for currEmployee in empList:
			cursor.execute("""
				INSERT INTO Project_Workers (p_id, employee)
				VALUES (%s,%s);"""%(projectID, currEmployee))
				
	#updates the title and description of a project			
	def updateProject(self, title, description, projectID):
		cursor = connection.cursor()
		cursor.execute("""
			UPDATE PROJECT
			SET title=%s, description=%s
			WHERE p_id=%s"""%(title,description,projectID))
	
	#deletes an employee from a project
	def deleteEmployee(self, employee, projectID):
		cursor = connection.cursor()
		cursor.execute("""
			DELETE FROM Project_Workers
			WHERE p_id=projectID AND employee=%s"""%(employee))
			
	
			
class ProjectWorkersManager(models.Manager):		
	#given an email, returns the list of users on thast project
	def getUsersProjects(self,user_email):
		cursor = connection.cursor()
		cursor.execute("""
			SELECT Project.p_id , Project.title
			FROM Project , Project_Workers
			WHERE Project.p_id = Project_Workers.project
			AND Project_Workers.employee =%s
			ORDER BY Project.p_id ASC;"""%(user_email))
		result_list = []
		for row in cursor.fetchall():
			p = Project(
				p_id=row[0],
				title=row[1])
			result_list.append(p)
		return result_list

class MilestoneManager(models.Manager):
	
	def addMilestone(self, name, description, due, projectID):
		cursor = connection.cursor()
		cursor.execute("""
			INSERT INTO Milestone
			( project , name , description , due_date , open )
			VALUES (%s, %s, %s, %s, True);"""%(projectID,name,description,due))
			
	
	#i'm not sure if this method name is accurate.	
	def updateMilestone(self, name, oldName, projectID):
		cursor = connection.cursor()
		cursor.execute("""
			INSERT INTO Milestone
			( description , due_date , open , name , project )
			VALUES (
				SELECT description , due_date , open , %s , project
				FROM Milestone
				WHERE name = %s AND project = %s
			);"""%(name, oldName, projectID))
	
	#returns the name, due date and status of a projects milestones
	def getMilestones(self,projectID):
		cursor = connection.cursor()
		cursor.execute("""
			SELECT name, due_date, open
			FROM Milestone
			WHERE project = %s;"""%(projectID))
		results_list = []
		for row in cursor.fetchall():
			p = self.model(
				name=row[0],
				due_date=row[1],
				open=row[2])
			result_list.append(p)
		return result_list
		
	#returns the description, due date and status of a projects milestones
	def getMilestonesInfo(self, name, projectID):
		cursor = connection.cursor()
		cursor.execute("""
			SELECT description, due_date, open
			FROM Milestone
			WHERE name = %s, project = %s;"""%(name,projectID))
		results_list = []
		for row in cursor.fetchall():
			p = self.model(
				description=row[0],
				due_date=row[1],
				open=row[2])
			result_list.append(p)
		return result_list
		
		
class TaskManager(models.Manager):
	#returns the number of tasks within a given project's milestone
	def countTasks(self,milestone,projectID):
		cursor = connection.cursor()
		cursor.execute("""
			SELECT COUNT (Name)
			FROM Task
			WHERE milestone = %s AND project = %s;"""%(milestone,projectID))
		#TODO figure out how a count comes back from a query
	
	#returns the number of open tasks within a given project's milestone
	def countOpenTasks(self,milestone,projectID):
		cursor = connection.cursor()
		cursor.execute("""
			SELECT COUNT (Name)
			FROM Task
			WHERE milestone = %s AND project = %s
				AND open=true;"""%(milestone,projectID))
		#TODO figure out how a count comes back from a query
		
	def updateTask(self, name, oldName, projectID):

		cursor = connection.cursor()
		cursor.execute("""
			UPDATE Task
			SET milestone = %s
			WHERE milestone = %s AND project = %s;"""%(name,oldName,projectID))
	
	def updateEntry(self, name, oldName, taskName, projectID):

		cursor = connection.cursor()
		cursor.execute("""
			UPDATE Entry
			SET milestone = %s
			WHERE milestone = %s AND task = %s
			AND project = %s;"""%(name,oldName,taskName,projectID))
	
	def updateEntry(self, oldName, projectID):

		cursor = connection.cursor()
		cursor.execute("""
			DELETE FROM Milestone
			WHERE name = %s AND project = %s;"""%(oldName, projectID))
	
	#not sure what kind of objects this returns so it may not work
	def getUserTasks(self, milestone, projectID):
		cursor = connection.cursor()
		cursor.execute("""
			SELECT due_date, name, first_name, last_name
			FROM (
				Tasks INNER JOIN User
				ON Tasks.employee = User.email
				WHERE project = %s AND milestone = %s
			);"""%(projectID,milestone))
		results_list = []
		for row in cursor.fetchall():
			p = self.model(
				due_date=row[0],
				name=row[1],
				first_name=row[2],
				last_name=row[3])
			result_list.append(p)
		return result_list
	
#class ProjectWorkersManager(models.Manager):


class User(models.Model):
    email = models.CharField(max_length=150, primary_key=True)
    password = models.CharField(max_length=60, blank=True)
    certifications = models.CharField(max_length=3, blank=True)
    first_name = models.CharField(max_length=45, blank=True)
    last_name = models.CharField(max_length=45, blank=True)
    type = models.CharField(max_length=3, blank=True)
    objects = UserManager()
    class Meta:
        db_table = u'User'
		
class Project(models.Model):
    p_id = models.IntegerField(primary_key=True)
    title = models.CharField(max_length=90, blank=True)
    description = models.CharField(max_length=300, blank=True)
    manager = models.ForeignKey(User, db_column='manager', related_name='userProjects')
    objects = ProjectManager()    
class Meta:
        db_table = u'Project'
		
class ProjectWorkers(models.Model):
    project = models.ForeignKey(Project, db_column='project',primary_key=True, related_name='projectWorkers')
    employee = models.ForeignKey(User, db_column='employee', related_name='userProjectWorkers')
    objects = ProjectWorkersManager()
    class Meta:
        db_table = u'Project_Workers'
		
class Milestone(models.Model):
    name = models.CharField(max_length=90, primary_key=True)
    project = models.ForeignKey(Project, db_column='project', related_name='projectMilestones')
    open = models.IntegerField(null=True, blank=True)
    description = models.CharField(max_length=300, blank=True)
    due_date = models.DateField(null=True, blank=True)
    class Meta:
        db_table = u'Milestone'
		

class Task(models.Model):
    name = models.CharField(max_length=150, primary_key=True)
    milestone = models.ForeignKey(Milestone, db_column='milestone', related_name='milestoneTasks')
    project = models.ForeignKey(Milestone, db_column='project', related_name='projectTasks')
    description = models.CharField(max_length=45, blank=True)
    due_date = models.DateField(null=True, blank=True)
    open = models.IntegerField(null=True, blank=True)
    employee = models.CharField(max_length=150)
    class Meta:
        db_table = u'Task'
		
class Entry(models.Model):
    date = models.DateField(primary_key=True)
    task = models.ForeignKey(Task, db_column='task',related_name='taskEntries')
    hours = models.IntegerField(null=True, blank=True)
    milestone = models.ForeignKey(Task, db_column='milestone',related_name='milestoneEntries')
    project = models.ForeignKey(Task, db_column='project',related_name='projectEntries')
    class Meta:
        db_table = u'Entry'

class Administrator(models.Model):
    administrator = models.ForeignKey(User, db_column='administrator', primary_key=True,related_name='userAdministrators')
    employee = models.ForeignKey(User, db_column='employee',related_name='userEmployees')
    class Meta:
        db_table = u'Administrator'












